﻿using Aspose.Cells;
using ExcelImport.Entity;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Xml.Linq;

namespace ExcelImport.Controllers
{
    public class ExcelController : Controller
    {

        // GET: Excel
        public ActionResult Index()
        {
            return View();

        }
        public ActionResult Import(HttpPostedFileBase file, string selectTable)
        {
            //解析xml
            XElement root = XElement.Load(Server.MapPath("~/xml/" + selectTable));
            //查出config中所有的表
            IEnumerable<XElement> tables = root.Elements("Table");
            //操作当前配置表
            if (tables.Count() > 1)
            {
                return MultiTable(file, tables);//配置为多表时
            }
            else
            {
                if (tables.Count() == 1)
                {
                    XElement table = tables.FirstOrDefault();
                    return singleTable(file, table);//配置为单表时
                }
                else
                {
                    //返回请配置xml
                    return Content("");
                }
            }
        }

        #region sql语句生成
        /// <summary>
        /// 生成insert语句
        /// </summary>
        /// <returns></returns>
        public SqlModel generateInsert(IEnumerable<XElement> columns, string tableName, Row dataRow)
        {
            //根据xml配置列，搜索上传的excel文件对应行、对应列的数据
            //字段列表
            Dictionary<string, SqlValueModel> fieldsList = new Dictionary<string, SqlValueModel>();
            MsgModel value = null;
            foreach (var col in columns)
            {
                value = ExcelImportHelp.GetColFinallyValue(dataRow, col);
                fieldsList.Add(
                 (string)col.Attribute("ColumnName"),//列名
                     new SqlValueModel()
                     {
                         Value = $"'{value.Value}'",//对应行对应列的最终值
                         Status = value.Status
                     }
                );
            }
            string sqlInsert = $@"insert into {tableName}({string.Join(
                   ",",
                  fieldsList.Keys
                  )}) values({string.Join(
                   ",",
                  fieldsList.Values.Select(m => m.Value)
                  )})";
            if (fieldsList.Values.Any(m => m.Status == false) != true)//判断当前dic是否存在异常数据
            {//不存在异常数据
             //字段对应的值
                return new SqlModel()
                {
                    Sql = sqlInsert,
                    Status = true,
                    Msg = value.Msg
                };
            }
            else
            {
                return new SqlModel()
                {
                    Sql = "",
                    Status = false,
                    Msg = value.Msg
                }; //表示当前sql语句存在异常数据
            }
        }
        public SqlModel generateUpdate(IEnumerable<XElement> columns, string tableName, Row dataRow)
        {
            List<SqlUpdateModel> fieldsList = new List<SqlUpdateModel>();
            MsgModel value = null;
            foreach (var col in columns)
            {
                value = ExcelImportHelp.GetColFinallyValue(dataRow, col);
                bool IsPrimaryKey = (string)col.Attribute("IsPrimaryKey")
                    == "true" ? true : false;
                fieldsList.Add(new SqlUpdateModel()
                {
                    KeyValuePair = $"{(string)col.Attribute("ColumnName")}='{value.Value}'",
                    Status = value.Status,
                    IsPrimaryKey = IsPrimaryKey
                });
            }
            var setList = fieldsList.Where(m => m.IsPrimaryKey == false).Select(m => m.KeyValuePair);
            var whereList = fieldsList.Where(m => m.IsPrimaryKey == true).Select(m => m.KeyValuePair);
            string sqlUpdate = $@"update {tableName}
            set {string.Join(",", setList)} 
            where {
                string.Join(" and ", whereList)
            }
            ";
            if (fieldsList.Any(m => m.Status == false) != true)//判断当前dic是否存在异常数据
            {//不存在异常数据
             //字段对应的值
                return new SqlModel()
                {
                    Sql = sqlUpdate,
                    Status = true,
                    Msg = value.Msg
                };
            }
            else
            {
                return new SqlModel()
                {
                    Sql = "",
                    Status = false,
                    Msg = value.Msg
                }; //表示当前sql语句存在异常数据
            }
        }
        #endregion


        public ActionResult singleTable(HttpPostedFileBase file, XElement table)
        {
            int goodCount = 0; //导入成功条数

            //错误日志列表
            List<string> errorLogList = new List<string>();
            //获得上传文件excel对象
            Workbook workbook = new Workbook(file.InputStream);
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;
            List<Cell> list = new List<Cell>();
            //init表头
            foreach (var item in cells.Rows[0])
            {
                list.Add(item as Cell);
            }
            HttpContext.Items.Add("TableHeader", list);
            //当前表所有列
            IEnumerable<XElement> columns =
               from element in table.Elements("Column")
               where (string)element.Attribute("IsDelete") != "true"
               select element;
            string currentTableName = (string)table.Attribute("Name");
            workbook = ErrorExcelHelp.deleteUnnecessary(workbook, columns); //删除表格中不必要的列
            var rows = cells.Rows;
            ErrorExcelHelp errorExcel = new ErrorExcelHelp(file.InputStream, columns);//初始化错误Workbook
            foreach (Row row in rows)
            {
                if (row.Index == 0 || row.IsBlank == true)//跳过表头与空行
                {
                    continue;
                }
                //数据行跳过属性应用,仅更新时有效
                string skip = (string)table.Attribute("Skip");
                if (!string.IsNullOrEmpty(skip))
                {
                    var skipResult = ExcelImportHelp.Skip(table, row, columns);
                    if (skipResult.IsSuccess == true)
                    {
                        //执形下一行
                        continue;
                    }
                }
                // 数据验证 (哪行、哪列)
                var verificationResult = DataVerification.verification(columns, currentTableName, row, (string)table.Attribute("Fitler"));
                if (verificationResult.IsSuccess == false)//数据验证失败跳过当前行
                {
                    errorLogList.Add(verificationResult.Msg);
                    errorExcel.AddWorkbookRow(columns, row, verificationResult.Msg);//添加错误Excel行
                    continue;
                }
                var type = ExcelImportHelp.IsPrimaryKey(columns, currentTableName, row);
                SqlModel SqlModel = null;
                if (type == ImportType.Insert)
                {
                    SqlModel = generateInsert(columns, currentTableName, row);
                }
                else
                {
                    SqlModel = generateUpdate(columns, currentTableName, row);
                }
                //生成sql语句
                if (SqlModel.Status == true)
                {
                    try
                    {
                        //待优化性能
                        using (var db = new SqlSugarClient(@"server=192.168.0.101,1433\sql2008R2;uid=sa;pwd=landa;database=best"))
                        {
                            int flag = db.ExecuteCommand(SqlModel.Sql);
                            if (flag > 0)
                            {
                                ++goodCount;
                                errorLogList.Add($"第{row.Index}行导入成功");
                            }
                            else
                            {
                                errorLogList.Add($"第{row.Index}行导入失败,0行受影响");
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        errorLogList.Add($"第{row.Index}行导入失败,{ex.Message}");

                    }
                    catch (Exception ex)
                    {
                        errorLogList.Add($"第{row.Index}行导入失败,{ex.Message}");
                    }
                }
                else
                {
                    errorLogList.Add($"第{row.Index}行导入失败");
                }
            };
            //导入的Json结果集
            string path = errorExcel.SaveWorkbook(Server, currentTableName);//生成错误的Excel
            string tips = ExcelImportHelp.importResult(errorLogList, goodCount, path);
            return Content(tips, "application/json");
        }

        /// <summary>
        /// xml多表导入
        /// </summary>
        /// <param name="file">http文件</param>
        /// <param name="tables">xml表配置</param>
        /// <returns></returns>
        public ActionResult MultiTable(HttpPostedFileBase file, IEnumerable<XElement> tables)
        {
            #region 初始化导入配置
            int goodCount = 0; //导入成功条数
                               //所有表所有列
            IEnumerable<XElement> allTableAllColumns =
               from element in tables.Elements("Column")
               where (string)element.Attribute("IsDelete") != "true"
               select element;
            ErrorExcelHelp errorExcel = new ErrorExcelHelp(file.InputStream, allTableAllColumns);//多表时初始化错误Workbook
            //错误日志列表
            List<string> errorLogList = new List<string>();
            //获得上传文件excel对象
            Workbook workbook = new Workbook(file.InputStream);
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;
            List<Cell> list = new List<Cell>();
            //init表头
            foreach (var item in cells.Rows[0])
            {
                list.Add(item as Cell);
            }
            HttpContext.Items.Add("TableHeader", list);





            //多表时初始化上传上来的excel
            workbook = ErrorExcelHelp.deleteUnnecessary(workbook, allTableAllColumns); //删除表格中不必要的列
            #endregion



            var rows = cells.Rows;

            foreach (Row row in rows)
            {
                if (row.Index == 0 || row.IsBlank == true)//跳过表头与空行
                {
                    continue;
                }

                //多表时成功次数的逻辑为：每行数据，生成的每条sql执形成功(每张配置表生成一条sql)
                int tempSuccessTimes = 0;
                foreach (var table in tables)
                {

                    string currentTableName = (string)table.Attribute("Name");
                    //当前表所有列
                    IEnumerable<XElement> columns =
                       from element in table.Elements("Column")
                       where (string)element.Attribute("IsDelete") != "true"
                       select element;

                    //数据行跳过属性应用,仅更新时有效
                    string skip = (string)table.Attribute("Skip");
                    if (!string.IsNullOrEmpty(skip))
                    {
                        var skipResult = ExcelImportHelp.Skip(table, row, columns);
                        if (skipResult.IsSuccess == true)//此时应该跳过所有表当前excel行
                        {
                            //执形下一Excel行
                            break;
                        }
                    }
                    // 数据验证 (哪行、哪列)
                    var verificationResult = DataVerification.verification(columns, currentTableName, row, (string)table.Attribute("Fitler"));
                    if (verificationResult.IsSuccess == false)//数据验证失败跳过当前excel行
                    {
                        errorLogList.Add(verificationResult.Msg);
                        errorExcel.AddWorkbookRow(columns, row, verificationResult.Msg);//添加错误Excel行
                        break;
                    }
                    var type = ExcelImportHelp.IsPrimaryKey(columns, currentTableName, row);
                    SqlModel SqlModel = null;
                    if (type == ImportType.Insert)
                    {
                        SqlModel = generateInsert(columns, currentTableName, row);
                    }
                    else
                    {
                        SqlModel = generateUpdate(columns, currentTableName, row);
                    }
                    //生成sql语句
                    if (SqlModel.Status == true)
                    {
                        try
                        {
                            //待优化性能
                            using (var db = new SqlSugarClient(@"server=192.168.0.101,1433\sql2008R2;uid=sa;pwd=landa;database=best"))
                            {
                                int flag = db.ExecuteCommand(SqlModel.Sql);
                                if (flag > 0)
                                {
                                    tempSuccessTimes++;
                                }
                                else
                                {
                                    errorLogList.Add($"第{row.Index}行导入失败,0行受影响");
                                    break;
                                }
                            }
                        }
                        catch (SqlException ex)
                        {
                            errorLogList.Add($"第{row.Index}行导入失败,{ex.Message}");
                            break;

                        }
                        catch (Exception ex)
                        {
                            errorLogList.Add($"第{row.Index}行导入失败,{ex.Message}");
                            break;
                        }
                    }
                    else
                    {
                        errorLogList.Add($"第{row.Index}行导入失败");
                        break;
                    }
                }
                if (tempSuccessTimes == tables.Count())
                {
                    //此时为成功一行数据
                    errorLogList.Add($"第{row.Index}行导入成功");
                    ++goodCount;
                }
                //这里配合内层循环的break; 不应再写逻辑。直接到下一次循环
            }
            string errorExcelName = (string)(tables.FirstOrDefault().Attribute("Name"));
            //导入的Json结果集
            string path = errorExcel.SaveWorkbook(Server, errorExcelName);//生成错误的Excel
            string tips = ExcelImportHelp.importResult(errorLogList, goodCount, path);
            return Content(tips, "application/json");
        }
    }
}